<?php
/** Copyright 2014 TinPhp Systems, Inc.
 * @author zhangxilian 2013-1-20
 * @package TinPhp
 */
/**
 * Oracle数据库链接对象，使用Adodb5.
 */
class TinDao_Oracle extends TinDaoBase {

	/** 实体化OracleDAO类 
	 * @param db对象 $dbConn 数据库链接对象
	 * @param view类 $view 表、视图对象
	 * */
	public function TinDao_Oracle(& $dbConn, & $view) {
		$this->db = $dbConn;
		$this->view = $view;
		$this->table = $view->getTableStmt();
		$this->tableSeq = $view->getTableSeq();
		$this->tableId = $view->getId();
		$this->seqOff = $view->getSeqOff();
		$this->logger = new TinLogger(SQL_LOG_PATH, "TinDao_Oci8");
	}
	
	//===========================================================================================
	//********查************
	//===========================================================================================	
	/** 
	 * 根据条件分页查询记录，各个参数值列名都要是 列别名
	 * @param string $cols 指定列名
	 * @param string $cond 指定查询条件
	 * @param int $page 当前分页数，默认1
	 * @param int $size 每页数量,默认10条
	 * @param array $params 可以执行绑定变量使用 array类型
	 * @param string $extra 语句的最后附加条件
	 * @return array
	 * */
	public function selectByCond($cols, $cond, $page = 1, $size = 10, $params = false, $extra = null) {		
		$colStmt = $this->convColStmt($cols);		
		if ($page || $size) {
			if(!$page)$page=1;
			$colNames = $this->convColStmt($this->view->getNames());
			$sql = "select $colStmt from ( select sjzc.*, ROWNUM rn from (select $colNames from " . $this->table . " ".$this->view->getTableLeftJoin();
		} else {
			$sql = "select $colStmt  from " . $this->table;
		}		

		$condStmt = "";
		if ($this->view->isMultiple()) {
			$conj = $this->view->getTableStmtCond();
			$condStmt = $conj . " AND (" . $cond . ")";
		} else {
			$condStmt .= $cond;
		}
		if ($page) {
			$condStmt = " where " . $condStmt;
		}

		if ($page) {
			$sqlStmt = $sql . "  $condStmt) sjzc where ROWNUM <=" . ($page * $size) . ") where rn > " . (($page -1) * $size) . "  ";
		} else{
			if ($size) { //未设置页面 设置大小
				$sqlStmt = $sql . " $condStmt ) sjzc where ROWNUM <=" . $size . ") where rn > 0";
			} else {
				$sqlStmt = $sql . " where " . $condStmt;
			}
		}		

		if ($extra != null) {
			$sqlStmt .= " " . $extra;
		}
		try{
            $this->logger->info("selectByCond:".$sqlStmt."   ".(is_array($params) ? "params:".implode('_',$params) :""));
			$respArray = $this->db->GetAll($sqlStmt, $params);
			return $respArray;
		}catch(Exception $e){
			$this->logger->error("selectByCond:".$sql."\n ERROR:".$e->getMessage());
			throw new DBException("selectByCond Fail: ".$e->getMessage());
		}
		
	}

	//===========================================================================================
	//********增************
	//===========================================================================================
	/**
	 * 插入数据记录
	 * @param TinDataMap $zxData 数据对象 @see TinDataMap
	 * @param bool $checkDataType 是否检查数据类型，默认true
	 * @param bool $tick 是否返回最后的ID值，默认true
	 * @param array $params 可以执行绑定变量使用 array类型
     * @return int
	 */
	public function insert(& $zxData, $checkDataType = true, $tick = true, $params=false) {
		//验证数据类型
		if ($checkDataType == true && $zxData->checkTypeValid() == false) {
			$this->logger->error("insert:".$this->table . " insert checkTypeValid Fail");
			echo $this->table . " insert checkTypeValid Fail";
			return false;
		}
		//获取表序号
		if ($this->seqOff != "off") {   //$this->tableSeq != "" && 
			$zxData->addEntry($this->tableId, $this->tableSeq . ".NEXTVAL", DB::INT);
		}

		$cols = $zxData->getKeys();
		$vals = $zxData->getValues();
		$types = $zxData->getTypes();
		$clobArr = array ();
		$blobArr = array ();

		$colStmt = "";
		$colsNum = count($cols);
		for ($i = 0; $i < $colsNum; $i++) {
			if ($types[$i] !== DB::BLOB && $types[$i] !== DB::CLOB) { //zhangxilian 加入CLOB、BLOB批量处理
				$colStmt .= $cols[$i];
				if ($i < $colsNum -1)
					$colStmt .= ",";
			}
		}

		$valStmt = "";
		$valsNum = count($vals);
		for ($i = 0; $i < $valsNum; $i++) {
			if ($types[$i] == DB::BLOB) {
				$blobArr[$cols[$i]] = $vals[$i]; //zhangxilian 加入BLOB批量处理
			} elseif ($types[$i] == DB::CLOB) {
				$clobArr[$cols[$i]] = $vals[$i]; //zhangxilian 加入CLOB批量处理
			} else {
				if(""!=$valStmt) $valStmt .=",";
				if ($this->needAppendQuote($types[$i])) {
					$valStmt .= $this->db->qstr($vals[$i]);
					//$valStmt .= "'" . $vals[$i] . "'";
				} else {
					$valStmt .= $vals[$i];
				}
			}
		}

		$sql = "insert into " . $this->table . " (" . $colStmt . ") values(" . $valStmt . ")";
		//$sql_id = "select " . $this->tableSeq . ".currval  from dual";
		
		try{
            $this->logger->info("selectByCond:".$sql."   ".(is_array($params) ? "params:".implode('_',$params) :""));
			$this->db->Execute($sql, $params);	
			//zhangxilian 加入CLOB、BLOB批量处理
             
            if ($this->seqOff != "off") {
                $lastInsertID = $this->getLastInsertId($this->tableSeq);
            }else{
                $lastId_Sql = "Select Max(".$this->tableId.") as Z_MAXID From $this->table";                
                $res_lastInsertID = $this->db->GetRow($lastId_Sql, null);
                $lastInsertID = $res_lastInsertID['z_maxid'] ? $res_lastInsertID['z_maxid'] : $res_lastInsertID['Z_MAXID'];
            }
			if ($lastInsertID) {
				if (count($blobArr) > 0) {
					foreach ($blobArr as $zxk => $zxv) {
						$this->updateBlob($zxk, $zxv, $this->tableId . "=" . $lastInsertID);
					}
				}
				if (count($clobArr) > 0) {
					foreach ($clobArr as $zxkc => $zxvc) {
						$this->updateClob($zxkc, $zxvc, $this->tableId . "=" . $lastInsertID);
					}
				}
			}	
			if ($tick) {
				return $lastInsertID; //$this->getLastInsertId($this->tableSeq);
			} else {
				return true;
			}
		}catch(Exception $e){
			$this->logger->error("insert:".$sql."\n ERROR:".$e->getMessage());
			throw new DBException("insert Fail: ".$e->getMessage());
		}
	}
	/**
	 * 获取最后插入ID
	 */
	public function getLastInsertId($table_name_seq = null) {
        try{            
            if ($table_name_seq) {   
                $this->logger->info("getLastInsertId:select " . $table_name_seq . ".currval from dual");
                $id = $this->db->GetRow("select " . $table_name_seq . ".currval  from dual");
                return $id[0];
            } else {
                $this->logger->info("getLastInsertId:select LAST_INSERT_ID()");
                $id = $this->db->GetRow("select LAST_INSERT_ID()");               
                return $id[0];
            }
        }catch(Exception $e){
			$this->logger->error("getLastInsertId: tablie_name_seq=".$table_name_seq."\n ERROR:".$e->getMessage());
			throw new DBException("getLastInsertId Fail: ".$e->getMessage());
		}
	}
	//===========================================================================================
	//********改************
	//===========================================================================================
	/** 根据指定ID，更新数据记录
     * @param TinDataMap $zxData 数据对象 @see TinDataMap
     * @param int $id 指定数据ID
     * @param bool $checkDataType 是否检测数据类型，默认true
     * @return int
     * */
	public function updateByID(& $zxData, $id, $checkDataType = true){
        try{
            return $this->updateByCond($zxData, $this->view->getId()."=".$id, $checkDataType);
        }catch(Exception $e){
			$this->logger->error("updateByID:".$e->getMessage());
			throw new DBException("updateByID Fail: ".$e->getMessage());
		}
	}
	 /** 根据条件更新数据，返回影响行数
     * @param TinDataMap $zxData 数据对象 @see TinDataMap
     * @param string $cond 查询条件(字段1=条件1 and 字段2=条件2)
     * @param bool $checkDataType 是否检测数据类型，默认true
     * @param bool $params 可以执行绑定变量使用 array类型
     * @param string $extra 语句的最后附加条件
     * @return int
     * */
	public function updateByCond(& $zxData, $cond, $checkDataType = true, $params = false, $extra = null) {
		if (is_null($cond) || $cond == "") {
			throw new DBException("Lack of Condition when process update");
		}
		//验证数据类型
		if ($checkDataType == true && $zxData->checkTypeValid() == false) {
			$this->logger->error("updateByCond:".$this->table . " update checkTypeValid Fail");
			throw new DBException("updateByCond: ".$this->table . " update checkTypeValid Fail");
			return;
		}

		$cols = $zxData->getKeys();
		$vals = $zxData->getValues();
		$types = $zxData->getTypes();
		$clobArr = array ();
		$blobArr = array ();

		$sql = "update " . $this->table . " set ";
		
		$fieldStr = "";
		$colsNum = count($cols);
		$fieldParams = array();	
		for ($i = 0; $i < $colsNum; $i++) {
			if ($types[$i] == DB::BLOB) {
				$blobArr[$cols[$i]] = $vals[$i]; //zhangxilian 加入BLOB批量处理
			}elseif ($types[$i] == DB::CLOB) {
				$clobArr[$cols[$i]] = $vals[$i]; //zhangxilian 加入CLOB批量处理
			} else {
				if(""!=$fieldStr) $fieldStr.=",";
				if(is_array($params)){
					$fieldStr .= $cols[$i] . "=:PK_".$cols[$i];
					if(strtoupper($vals[$i])=="SYSDATE") $vals[$i]=date("Y-m-d H:i:s");
					$fieldParams["PK_".$cols[$i]] = $vals[$i];
				}else{
					$fieldStr .= $cols[$i] . " = ";
					if ($this->needAppendQuote($types[$i])) {
						$fieldStr .= $this->db->qstr($vals[$i]);
						//$fieldStr .= "'" . $vals[$i] . "'";
					} else {
						$fieldStr .= $vals[$i];
					}
				}
			}
		}
		if(is_array($params)){
			$params = array_merge($fieldParams, $params);			
		}
		
		$sql .= $fieldStr . " where " . $cond;
		$sqlStmt = $sql;
		if ($extra != null) {
			$sqlStmt .= " " . $extra;
		}
		try{	
            $this->logger->info("updateByCond:".$sqlStmt."   ".(is_array($params) ? "params:".implode('_',$params) :""));
			if($this->db->Execute($sqlStmt, $params)){
				//解决：如果使用绑定变量模式更新不要 加入BLOB或CLOB字段时，请另外调updateBlob方式更新($params==array时为绑定变量模式)
				// 如果条件中是字符串的 要求 加单引号 如条件是"TITLE=:PKTITLE" 那么 绑定变量时 array("PKTITLE"=>"'a'")
				$condTmp = $cond;
				if(is_array($params)){
					foreach($params as $kk=>$vv){
						$condTmp = str_replace(":$kk",$vv,$condTmp);
					}
				}
	//			try{
					//zhangxilian 加入BLOB批量处理
					if (count($blobArr) > 0) {
						foreach ($blobArr as $zxbk => $zxbv) {
							$this->updateBlob($zxbk, $zxbv, $condTmp." ". $extra);
						}
					}
					//zhangxilian 加入CLOB批量处理
					if (count($clobArr) > 0) {
						foreach ($clobArr as $zxck => $zxcv) {
							$this->updateClob($zxck, $zxcv, $condTmp." ". $extra);
						}
					}
	//			}catch(Exception $e){
	//				echo $e->getMessage();
	//				return false;
	//			}
				return true;
			}else{
				return false;
			}
		}catch(Exception $e){
			$this->logger->error("updateByCond".$sqlStmt."\n ERROR:".$e->getMessage());
			throw new DBException("updateByCond Fail: ".$e->getMessage());
		}
	}
	/** 更新Clob类型字段数据 
     * @param string $cols 字段名称
     * @param string $val 值
     * @param string $cond 查询条件(字段1=条件1 and 字段2=条件2)
     * */
	public function updateClob($cols, $val, $cond) {
        try{
            $this->logger->info("updateClob: ".$this->table." | ".$cols." | ".$val." | ".$cond);
            return $this->db->UpdateClob($this->table, $cols, $val, $cond);
        }catch(Exception $e){
			$this->logger->error(" updateClob:".$e->getMessage());
			throw new DBException("updateClob Fail: ".$e->getMessage());
		}
	}
	/** 更新Blob类型字段数据 
     * @param string $cols 字段名称
     * @param string $val 值
     * @param string $cond 查询条件(字段1=条件1 and 字段2=条件2)
     * @param string $blobtype Oracle特有的字段类型CLOB、BLOB 
     * */
	public function updateBlob($cols, $val, $cond, $blobtype = 'BLOB') {
        try{
            $this->logger->info("updateBlob: ".$this->table." | ".$cols." | ".$val." | ".$cond);
            return $this->db->UpdateBlob($this->table, $cols, $val, $cond, $blobtype);
        }catch(Exception $e){
			$this->logger->error("updateBlob:".$e->getMessage());
			throw new DBException("updateBlob Fail: ".$e->getMessage());
		}
	}
	/**
	 * 同步索引
	 * @param string $idx 索引名称
	 * @param string $memory 指定同步索引需要的内存。默认是系统参数DEFAULT_INDEX_MEMORY 。指定一个大的内存时候可以加快索引效率和查询速度，且索引有较少的碎片
	 * @param string $part_name同步哪个分区索引。
	 * @param string $parallel_degree 并行同步索引。设置并行度。
     * */	 
	public function updateIndex($idx, $memory, $part_name = null, $parallel_degree = null) {
		$sql = "BEGIN ctx_ddl.sync_index('$idx', '$memory', '$part_name', '$parallel_degree'); END; ";
        try{
            $this->logger->info("updateIndex: ".$sql);
            $this->db->Execute($sql);
        }catch(Exception $e){
			$this->logger->error("updateIndex: ".$sql." ERROR:".$e->getMessage());
			throw new DBException("updateIndex Fail: ".$e->getMessage());
		}
	}		
	//===========================================================================================
	//********删************
	//===========================================================================================
	/** 根据指定条件，删除数据记录
     * @param string $cond 查询条件(字段1=条件1 and 字段2=条件2)
     * @param bool $params 可以执行绑定变量使用 array类型
     * @param string $extra 语句的最后附加条件
     * @return int
     * */
	public function deleteByCond($cond, $params = false, $extra = null) {
		//$sql = "delete from " . $this->table . " $extra where " . $cond;
		$sql = "";
		if (TIN_DB_TYPE == "oci8" || TIN_DB_TYPE == "oracle") // oracle
		{
			$sql = "delete from " . $this->table . " commit where " . $cond;
		} else {
			$sql = "delete from " . $this->table . "  where " . $cond;
		}
		$sqlStmt = $sql;
		if ($extra != null) {
			$sqlStmt .= " " . $extra;
		}
		try{
            $this->logger->info("deleteByCond:".$sqlStmt."   ".(is_array($params) ? "params:".implode('_',$params) :""));
			return $this->db->Execute($sqlStmt, $params);
		}catch(Exception $e){
			$this->logger->error("deleteByCond:".$sqlStmt."\n ERROR:".$e->getMessage());
			throw new DBException("deleteByCond Fail: ".$e->getMessage());
		}
	}	

//	// debug
//	public function getTable() {
//		return $this->table;
//	}
	
}//End Class
?>
